//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.sale;

import java.sql.*;
import java.util.Vector;
import org.jpgolay.me.persistence.OLD_RequestDB;
import org.jpgolay.me.util.MEDate;

public class OLD_SaleSQLList {

    public OLD_SaleSQLList() {
        resultat = false;
        dbr = new OLD_RequestDB();
    }

    public boolean searchBy(int select, int prefixe) {
        switch (select) {
            case 18: // '\022'
                selectForPieceCC(prefixe);
                break;

            case 19: // '\023'
                selectForPieceNO(prefixe);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int prefixe, String like) {
        switch (select) {
            case 20: // '\024'
                selectForLikePieceCC(prefixe, like);
                break;

            case 21: // '\025'
                selectForLikePieceNO(prefixe, like);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int prefixe, String du, String au) {
        switch (select) {
            case 1: // '\001'
                selectByClientAllAG(prefixe, du, au);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int prefixe, int agenceId, String du, String au) {
        switch (select) {
            case 1: // '\001'
                selectByClient(prefixe, agenceId, du, au);
                break;

            case 2: // '\002'
                selectByCodeClient(prefixe, agenceId, du, au);
                break;

            case 3: // '\003'
                selectByNumber(prefixe, agenceId, du, au);
                break;

            case 7: // '\007'
                selectByEcheance(prefixe, agenceId, du, au);
                break;

            case 8: // '\b'
                selectByResteDu(prefixe, agenceId, du, au);
                break;

            case 4: // '\004'
                selectByGrpBlClient(prefixe, agenceId, du, au);
                break;

            case 5: // '\005'
                selectByGrpBlCodeClient(prefixe, agenceId, du, au);
                break;

            case 6: // '\006'
                selectByGrpBlNumber(prefixe, agenceId, du, au);
                break;

            case 16: // '\020'
                selectByLivrePar(prefixe, agenceId, du, au);
                break;
        }
        return resultat;
    }

    public boolean searchById(int select, int prefixe, int agenceId, String du, String au, String id) {
        switch (select) {
            case 9: // '\t'
                selectByCommercial(prefixe, agenceId, du, au, id);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, int prefixe, int agenceId, String du, String au, String like) {
        switch (select) {
            case 10: // '\n'
                selectByClientLike(prefixe, agenceId, du, au, like);
                break;

            case 11: // '\013'
                selectByCodeClientLike(prefixe, agenceId, du, au, like);
                break;

            case 12: // '\f'
                selectByNumberLike(prefixe, agenceId, du, au, like);
                break;

            case 13: // '\r'
                selectByGrpBlClientLike(prefixe, agenceId, du, au, like);
                break;

            case 14: // '\016'
                selectByGrpBlCodeClientLike(prefixe, agenceId, du, au, like);
                break;

            case 15: // '\017'
                selectByGrpBlNumberLike(prefixe, agenceId, du, au, like);
                break;

            case 17: // '\021'
                selectByLivreParLike(prefixe, agenceId, du, au, like);
                break;
        }
        return resultat;
    }

    public Vector getRangees() {
        return resultatsRangees;
    }

    public Vector getColonnes() {
        return resultatsColonnes;
    }

    private void selectForPieceCC(int prefixe) {
        String requete = "";
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='F' and ventes_factures.clients_id=clients.id order by CODE_CLIENT, NUMERO desc";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='C' and ventes.clients_id=clients.id order by CODE_CLIENT, NUMERO desc";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='D' and ventes.clients_id=clients.id order by CODE_CLIENT, NUMERO desc";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='BL' and ventes.clients_id=clients.id order by CODE_CLIENT, NUMERO desc";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='A' and ventes_factures.clients_id=clients.id order by CODE_CLIENT, NUMERO desc";
                break;
        }
        getResultat4Lines(requete);
    }

    private void selectForPieceNO(int prefixe) {
        String requete = "";
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='F' and ventes_factures.clients_id=clients.id order by NUMERO desc";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='A' and ventes_factures.clients_id=clients.id order by NUMERO desc";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='C' and ventes.clients_id=clients.id order by NUMERO desc";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='D' and ventes.clients_id=clients.id order by NUMERO desc";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='BL' and ventes.clients_id=clients.id order by NUMERO desc";
                break;
        }
        getResultat4Lines(requete);
    }

    private void selectForLikePieceCC(int prefixe, String like) {
        String requete = "";
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='F' and ventes_factures.clients_id=clients.id and clients_id like '" + like + "%' " + "order by CODE_CLIENT, NUMERO desc";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='A' and ventes_factures.clients_id=clients.id and clients_id like '" + like + "%' " + "order by CODE_CLIENT, NUMERO desc";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='C' and ventes.clients_id=clients.id and clients_id like '" + like + "%' " + "order by CODE_CLIENT, NUMERO desc";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='D' and ventes.clients_id=clients.id and clients_id like '" + like + "%' " + "order by CODE_CLIENT, NUMERO desc";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='BL' and ventes.clients_id=clients.id and clients_id like '" + like + "%' " + "order by CODE_CLIENT, NUMERO desc";
                break;
        }
        getResultat4Lines(requete);
    }

    private void selectForLikePieceNO(int prefixe, String like) {
        String requete = "";
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='F' and ventes_factures.clients_id=clients.id and ventes_factures.id like '" + like + "%' " + "order by NUMERO desc";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes_factures where prefixe='A' and ventes_factures.clients_id=clients.id and ventes_factures.id like '" + like + "%' " + "order by NUMERO desc";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='C' and ventes.clients_id=clients.id and ventes.id like '" + like + "%' " + "order by NUMERO desc";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='D' and ventes.clients_id=clients.id and ventes.id like '" + like + "%' " + "order by NUMERO desc";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( date, '%d/%m/%Y') as DATE, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT from clients, ventes where prefixe='BL' and ventes.clients_id=clients.id and ventes.id like '" + like + "%' " + "order by NUMERO desc";
                break;
        }
        getResultat4Lines(requete);
    }

    private void selectByClient(int prefixe, int agenceId, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' order by CLIENT";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByClientAllAG(int prefixe, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='F' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='A' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' order by CLIENT";
                break;

            case 5: // '\005'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, ventes_factures, commerciaux where date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.clients_id=clients.id " + "order by CLIENT";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByCodeClient(int prefixe, int agenceId, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, reglements_clients, clients, commerciaux where  facture_id=ventes_factures.id and pointage != 'Encaiss\351' and pointage != 'Pay\351' and prefixe='F' and  montant_regle != net_a_payer and montant_regle != 0 and ventes_factures.entreprise_id='" + agenceId + "' " + "and ventes_factures.date between  '" + date.toMysql(du) + "' " + "and '" + date.toMysql(au) + "' " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' order by CLIENT";
                break;

            case 5: // '\005'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' order by NUMERO";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByNumber(int prefixe, int agenceId, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and ventes_factures.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "order by NUMERO";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and ventes_factures.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "order by NUMERO";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by NUMERO";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "order by NUMERO";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' order by NUMERO";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByEcheance(int prefixe, int agenceId, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "'";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "'";
                break;

            case 1: // '\001'
                requete = "";
                break;

            case 0: // '\0'
                requete = "";
                break;

            case 2: // '\002'
                requete = "";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByResteDu(int prefixe, int agenceId, String du, String au) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and round(( net_a_payer - montant_regle ), 2 ) != 0 " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and round(( net_a_payer - montant_regle ), 2 ) != 0 " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "";
                break;

            case 0: // '\0'
                requete = "";
                break;

            case 2: // '\002'
                requete = "";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByLivrePar(int prefixe, int agenceId, String du, String au) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "'";
        getResultat7Lines(requete);
    }

    private void selectByClientLike(int prefixe, int agenceId, String du, String au, String like) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes_factures where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, ventes, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByCodeClientLike(int prefixe, int agenceId, String du, String au, String like) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and clients_id like '" + like + "%' " + "order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and clients_id like '" + like + "%' " + "order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and clients_id like '" + like + "%' " + "order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and clients_id like '" + like + "%' " + "order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and clients_id like '" + like + "%' " + "order by CLIENT";
                break;

            case 5: // '\005'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes_factures, clients, commerciaux where ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and clients_id like '" + like + "%' " + "order by NUMERO";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByNumberLike(int prefixe, int agenceId, String du, String au, String like) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.id like '" + like + "%' " + "order by NUMERO";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.id like '" + like + "%' " + "order by NUMERO";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and ventes.id like '" + like + "%' " + "order by NUMERO";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and ventes.id like '" + like + "%' " + "order by NUMERO";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.id like '" + like + "%' " + "order by NUMERO";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByLivreParLike(int prefixe, int agenceId, String du, String au, String like) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and livre like '" + like + "%' ";
        getResultat7Lines(requete);
    }

    private void selectByCommercial(int prefixe, int agenceId, String du, String au, String id) {
        String requete = "";
        MEDate date = new MEDate();
        switch (prefixe) {
            case 3: // '\003'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='F' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.commerciaux_id='" + id + "' order by CLIENT";
                break;

            case 4: // '\004'
                requete = "select ventes_factures.id as NUMERO, date_format( ventes_factures.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from commerciaux, ventes_factures, clients where prefixe='A' and ventes_factures.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes_factures.commerciaux_id=commerciaux.id " + "and ventes_factures.commerciaux_id='" + id + "' order by CLIENT";
                break;

            case 1: // '\001'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='C' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and ventes.commerciaux_id='" + id + "' order by CLIENT";
                break;

            case 0: // '\0'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from clients, commerciaux, ventes where prefixe='D' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id=commerciaux.id " + "and ventes.clients_id=clients.id " + "and ventes.commerciaux_id='" + id + "' order by CLIENT";
                break;

            case 2: // '\002'
                requete = "select ventes.id as NUMERO, date_format( ventes.date, '%d/%m/%Y') as DATE, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, round(( net_a_payer - montant_regle ), 2 ) as RESTE_DU, commerciaux.nom as COMMERCIAL from ventes, clients, commerciaux where prefixe='BL' and commerciaux.id=ventes.commerciaux_id and clients.id = ventes.clients_id and ventes.entreprise_id='" + agenceId + "' and ventes.date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.commerciaux_id='" + id + "' order by CLIENT";
                break;
        }
        getResultat7Lines(requete);
    }

    private void selectByGrpBlClient(int prefixe, int agenceId, String du, String au) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "order by CLIENT";
        getResultat7Lines(requete);
    }

    private void selectByGrpBlCodeClient(int prefixe, int agenceId, String du, String au) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "order by CODE_CLIENT";
        getResultat7Lines(requete);
    }

    private void selectByGrpBlNumber(int prefixe, int agenceId, String du, String au) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "order by NUMERO";
        getResultat7Lines(requete);
    }

    private void selectByGrpBlClientLike(int prefixe, int agenceId, String du, String au, String like) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "and clients.raison_sociale like '" + like + "%' " + "order by CLIENT";
        getResultat7Lines(requete);
    }

    private void selectByGrpBlCodeClientLike(int prefixe, int agenceId, String du, String au, String like) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "and clients_id like '" + like + "%' " + "order by CODE_CLIENT";
        getResultat7Lines(requete);
    }

    private void selectByGrpBlNumberLike(int prefixe, int agenceId, String du, String au, String like) {
        MEDate date = new MEDate();
        String requete = "select ventes.id as NUMERO, date_format( date_livraison, '%d/%m/%Y') as DATE_LIVR, clients_id as CODE_CLIENT, clients.raison_sociale as CLIENT, round(total_brut_ht, 2) as HT, round(net_a_payer, 2) as TTC, livre as LIVRE_PAR from clients, ventes where prefixe='BL' and no_fact =' ' and ventes.entreprise_id='" + agenceId + "' and date between '" + date.toMysql(du) + "' and '" + date.toMysql(au) + "' and ventes.clients_id=clients.id " + "and ventes.id like '" + like + "%' " + "order by NUMERO";
        getResultat7Lines(requete);
    }

    private void getResultat4Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                    resultatsRangees.addElement(rs.getString(4));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }

    private void getResultat7Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                    resultatsRangees.addElement(rs.getString(4));
                    resultatsRangees.addElement(rs.getString(5));
                    resultatsRangees.addElement(rs.getString(6));
                    resultatsRangees.addElement(rs.getString(7));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }
    private OLD_RequestDB dbr;
    private Vector resultatsRangees;
    private Vector resultatsColonnes;
    public static final int CLIENT = 1;
    public static final int CODE_CLIENT = 2;
    public static final int NUMERO = 3;
    public static final int GRP_BL_CLIENT = 4;
    public static final int GRP_BL_CODE_CLIENT = 5;
    public static final int GRP_BL_NUMERO = 6;
    public static final int ECHEANCE = 7;
    public static final int RESTE_DU = 8;
    public static final int COMMERCIAL_ID = 9;
    public static final int LIKE_CLIENT = 10;
    public static final int LIKE_CODE_CLIENT = 11;
    public static final int LIKE_NUMERO = 12;
    public static final int LIKE_GRP_BL_CLIENT = 13;
    public static final int LIKE_GRP_BL_CODE_CLIENT = 14;
    public static final int LIKE_GRP_BL_NUMERO = 15;
    public static final int LIVRE_PAR = 16;
    public static final int LIKE_LIVRE_PAR = 17;
    public static final int PIECE_CC = 18;
    public static final int PIECE_NO = 19;
    public static final int LIKE_PIECE_CC = 20;
    public static final int LIKE_PIECE_NO = 21;
    private final int DEVIS = 0;
    private final int COMMANDE = 1;
    private final int BL = 2;
    private final int FACTURE = 3;
    private final int AVOIR = 4;
    private final int FACTURE_AND_AVOIR = 5;
    private boolean resultat;
}
